Error: Failure executing database command

• Content Studio prior to ver. 5.2

When upgrading from Content Studio 5.0 or 5.1 to version 5.2 or later you receive the following error during the database upgrade process.

Failure executing database command
A error occured in step N batch 2 at line 10.

followed by

#FxFtxXmlIx.sql
System.componentModel.DoWorkEventargs: Timeout expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.

When you try the 'Retry' button other errors occurs and the database upgrade process is aborted.

This problem can occur on large site databases at the beginning of the affected step during which the database is upgraded to version 5.2.505 RC1 which is an intermediate step in the upgrade process to the release version of 5.2. This step changes the data type of the FullData field in the xml index table from nvarchar(3548) to nvarchar(max). This is done in order to make it possble to store, search and return all data in an EPT field. The data type nvarchar(max) is not supported until SQL Server 2005 and since Content Studio version 5.2 requires SQL Server 2005 or later this is an important change to make. However, in some instances of SQL Server this change can take a very long time and during that time SQL Server might stop answer.

You can manually execute the afftected batch and the continue the database upgrade manually.

Execute the following SQL Script in SQL Server Management Studio against the affected Content Studio database

--If needed, change the datatype of the fulltext indexed column
-- [FullData] from nvarchar(n) to nvarchar(max)
IF EXISTS (SELECT * FROM [sys].[columns] 
              WHERE [object_id] = OBJECT_ID('[dbo].[tbl_Xml_data]') 
                    AND [name]='FullData'
                    AND [system_type_id] = 231
                    AND [max_length] != -1) --nvarchar(n)         
   BEGIN
       --Disable the fulltext index
       ALTER FULLTEXT INDEX ON [dbo].[tbl_Xml_data] DISABLE;
       PRINT 'Fulltext is disabled';
       
       --Drop [FullData] from the fulltext index
       ALTER FULLTEXT INDEX ON [dbo].[tbl_Xml_data] DROP ([FullData]);
       --Drop the default constraint for [FullData]
       ALTER TABLE [dbo].[tbl_Xml_data]
           DROP CONSTRAINT [DF_XML_DataFullData];
       --Alter the data type to nvarchar(3548) to nvarchar(max)
       ALTER TABLE [dbo].[tbl_Xml_data] 
           ALTER COLUMN [FullData] nvarchar(max) NOT NULL;
       --Add the default constraint back
       ALTER TABLE [dbo].[tbl_Xml_data]
 	       ADD CONSTRAINT DF_XML_DataFullData  DEFAULT ('') FOR [FullData];
       --Add the column to the fulltext index
       ALTER FULLTEXT INDEX ON [dbo].[tbl_Xml_data] 
          ADD ([FullData] LANGUAGE 'Swedish')
       PRINT 'Datatype [FullData] is changed to nvarchar(max)';
       --Turn on automatic change_tracking if needed
       IF NOT EXISTS(SELECT * FROM sys.fulltext_indexes 
                       WHERE [object_id] = OBJECT_ID('[dbo].[tbl_Xml_data]')
                             AND [change_tracking_state] = 'A')
             BEGIN
                --Enable background change tracking
                ALTER FULLTEXT INDEX ON [dbo].[tbl_Xml_data] SET CHANGE_TRACKING AUTO;
                PRINT 'Fulltext CHANGE_TRACKING changed to AUTO';
             END
       
       ALTER FULLTEXT INDEX ON [dbo].[tbl_Xml_data] ENABLE;
       PRINT 'Fulltext is enabled';
  END            
                

This script might take a long time to perform so be patient. When the script was successfully executed you can continue the upgrade process by using the UpgradeCSDb tool that gets installed in the tools subdirectory under the Content Studio binary directory. When the database has been upgraded you should rebuild the file system by using the SiteContentManager tool.